﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace SP.OpenSource.CDC.Monitor.BLL
{
    class CDCManagement
    {
        public String GetCDCEnableScript(String sConnectionString, ref DataTable dtInCDCEnabledTables, ref String sErrorMessage)
        {
            try
            {
                String sInDDLEnableScript = String.Empty;

                String sCDCFunctionName = String.Empty;
                String sQuery = "select sch.name as schema_name,syst.name as table_name from sys.tables syst ";
			           sQuery += " left outer join sys.schemas sch on syst.schema_id = sch.schema_id";
                       sQuery += " where syst.name = 'change_tables' and syst.type = 'U' ";


                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                DbConnection connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQuery;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                String sCDCTableSchema = String.Empty;
                String sCDCTableName = String.Empty;

                DbDataReader schemaReader = command.ExecuteReader();
                while (schemaReader.Read())
                {
                    sCDCTableSchema = schemaReader["schema_name"].ToString();
                    sCDCTableName = schemaReader["table_name"].ToString();
                }
                schemaReader.Close();


                sQuery = "Select * from " + sCDCTableSchema + "." + sCDCTableName;

                command.CommandText = sQuery;
                command.Connection = connection;

                DataTable dtCDCEnabledTables = new DataTable();
                DbDataAdapter adapter = factory.CreateDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dtCDCEnabledTables);

                sErrorMessage = String.Empty;
                if (connection.State == ConnectionState.Open)
                    connection.Close();
                sInDDLEnableScript += "/* ";
                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += "Scripts to enable CDC on tables.";
                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += "Created on: " + DateTime.Now.ToString("dd/MMM/yyyy HH:mm:ss ");
                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += "Note: Only the CDC enabled tables will be included in the script.";
                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += "*/ ";

                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += Environment.NewLine;

                sInDDLEnableScript += "EXEC sys.sp_cdc_enable_db ";
                sInDDLEnableScript += Environment.NewLine;
                sInDDLEnableScript += "GO";
                sInDDLEnableScript += Environment.NewLine;

                foreach (DataRow drCDCTables in dtCDCEnabledTables.Rows)
                {
                    DataRow[] drSelected = dtInCDCEnabledTables.Select("capture_instance = '" + drCDCTables["capture_instance"].ToString() + "'");
                    if (drSelected.Length > 0)
                    {
                        sInDDLEnableScript += "EXEC sys.sp_cdc_enable_table ";
                        sInDDLEnableScript += "@source_schema = N'" + drSelected[0]["source_schema"].ToString() + "', ";
                        sInDDLEnableScript += Environment.NewLine;
                        sInDDLEnableScript += "@source_name = N'" + drSelected[0]["source_table"].ToString() + "', ";
                        sInDDLEnableScript += Environment.NewLine;
                        sInDDLEnableScript += "@role_name = N'" + drCDCTables["role_name"].ToString() + "', ";
                        sInDDLEnableScript += Environment.NewLine;
                        sInDDLEnableScript += "@capture_instance = N'" + drCDCTables["capture_instance"].ToString() + "', ";
                        sInDDLEnableScript += Environment.NewLine;
                        sInDDLEnableScript += "@supports_net_changes = N'" +( drCDCTables["supports_net_changes"].ToString() == "False" ? "0" : "1") + "', ";
                        sInDDLEnableScript += Environment.NewLine;

                        sInDDLEnableScript += "@index_name = N'" + (drCDCTables["index_name"].ToString().Length > 0 ? drCDCTables["index_name"].ToString() : "null") + "', ";
                        sInDDLEnableScript += Environment.NewLine;

                        sInDDLEnableScript += "@captured_column_list = N'" + (drSelected[0]["captured_column_list"].ToString().Length > 0 ? drSelected[0]["captured_column_list"].ToString() : "null") + "', ";
                        sInDDLEnableScript += Environment.NewLine;

                        sInDDLEnableScript += "@filegroup_name = N'" + (drCDCTables["filegroup_name"].ToString().Length > 0 ? drCDCTables["filegroup_name"].ToString() : "null") + "', ";
                        sInDDLEnableScript += Environment.NewLine;

                        sInDDLEnableScript += "@allow_partition_switch = N'" + (drCDCTables["partition_switch"].ToString()== "True" ? "1" : "0") + "' ";
                        sInDDLEnableScript += Environment.NewLine;


                        sInDDLEnableScript += Environment.NewLine;
                        sInDDLEnableScript += "GO";
                        sInDDLEnableScript += Environment.NewLine;
                    }
                }

                return sInDDLEnableScript;
            }
            catch (Exception ex)
            {
                sErrorMessage = "Verify the CDC is enabled on the database and user has access to CDC functions.";
                return String.Empty;
            }
        }
        
        public String IsUserSysAdmin(String sConnectionString, String sDBName, ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {
                String sQueryCheckDB = "SELECT IS_SRVROLEMEMBER ('sysadmin','') as  UsrRole";
                
                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQueryCheckDB;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DbDataReader rdrCDCCheck = command.ExecuteReader();
                String sOutStatus = String.Empty;
                while (rdrCDCCheck.Read())
                {
                    sOutStatus = rdrCDCCheck["UsrRole"].ToString();
                }
                sErrorMessage = String.Empty;


                rdrCDCCheck.Close();
                if (connection.State == ConnectionState.Open)
                    connection.Close();
                return sOutStatus;
            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return String.Empty;
            }
        }
        
        public String IsUserDBOwner(String sConnectionString, String sDBName, ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {
                String sQueryCheckDB = "SELECT IS_ROLEMEMBER ('db_owner')  as  UsrRole";

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQueryCheckDB;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DbDataReader rdrCDCCheck = command.ExecuteReader();
                String sOutStatus = String.Empty;
                while (rdrCDCCheck.Read())
                {
                    sOutStatus = rdrCDCCheck["UsrRole"].ToString();
                }
                sErrorMessage = String.Empty;


                rdrCDCCheck.Close();
                if (connection.State == ConnectionState.Open)
                    connection.Close();
                return sOutStatus;
            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return String.Empty;
            }
        }

        public bool IsCDCEnabledDB(String sConnectionString, String sDBName, ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {
                String sQueryCheckDB = "select is_cdc_enabled from sys.databases where is_cdc_enabled = 1 AND [name] = '";
                sQueryCheckDB += sDBName + "'";

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQueryCheckDB;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                DbDataReader rdrCDCCheck = command.ExecuteReader();
                String sOutStatus = String.Empty;
                while (rdrCDCCheck.Read())
                {
                    sOutStatus = rdrCDCCheck["is_cdc_enabled"].ToString();
                }
                sErrorMessage = String.Empty;
                
                
                rdrCDCCheck.Close();
                if (connection.State == ConnectionState.Open)
                    connection.Close();

                if (sOutStatus.Length > 0 && sOutStatus == "True")
                    return true;
                else
                    return false;
                                
            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return false;
            }
        }

        public DataTable GetCDCEnabledTables(String sConnectionString, ref String sErrorMessage)
        {
            DbConnection connection = null;
            DataTable dtCDCEnabledTables = new DataTable("CDCEnabledTables");
            try
            {
                String sQueryCDCEnabledTables = "execute [sys].[sp_cdc_help_change_data_capture]";
                
                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQueryCDCEnabledTables;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                DbDataAdapter adapter = factory.CreateDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dtCDCEnabledTables);

                sErrorMessage = String.Empty;
                if (connection.State == ConnectionState.Open)
                    connection.Close();

                if (null != dtCDCEnabledTables )
                {
                    if (dtCDCEnabledTables.Columns.Contains("object_id"))
                        dtCDCEnabledTables.Columns.Remove("object_id");
                    if (dtCDCEnabledTables.Columns.Contains("source_object_id"))
                        dtCDCEnabledTables.Columns.Remove("source_object_id");
                    if (dtCDCEnabledTables.Columns.Contains("start_lsn"))
                        dtCDCEnabledTables.Columns.Remove("start_lsn");
                    if (dtCDCEnabledTables.Columns.Contains("end_lsn"))
                        dtCDCEnabledTables.Columns.Remove("end_lsn");
                    if (dtCDCEnabledTables.Columns.Contains("create_date"))
                        dtCDCEnabledTables.Columns.Remove("create_date");
                    
                    dtCDCEnabledTables.AcceptChanges();
                    dtCDCEnabledTables.Columns.Add("table_fullname");
                    dtCDCEnabledTables.AcceptChanges();

                    foreach (DataRow drNew in dtCDCEnabledTables.Rows)
                    {
                        drNew["table_fullname"] = "[" + drNew["source_schema"].ToString() + "].[" + drNew["source_table"].ToString() + "]";
                    }
                    dtCDCEnabledTables.AcceptChanges();
                    
                }

                return dtCDCEnabledTables;
            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return null;
            }
        }

        public DataTable GetCDCTableColumns(String sConnectionString, String sSchemaName,String sTableName,ref String sErrorMessage)
        {
            DbConnection connection = null;
            DataTable dtCDCTableColumnss = new DataTable("CDCTableColumnss");
            try
            {
                String sQueryCDCTableColumns = "select 0 as COLUMN_SELECTION,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = '" + sTableName + "'";
                sQueryCDCTableColumns += " AND TABLE_SCHEMA = '" + sSchemaName + "'";
                sQueryCDCTableColumns += " AND COLUMN_NAME NOT IN ('__$start_lsn','__$end_lsn','__$seqval','__$operation','__$update_mask')";

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sQueryCDCTableColumns;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                DbDataAdapter adapter = factory.CreateDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(dtCDCTableColumnss);

                sErrorMessage = String.Empty;
                if (connection.State == ConnectionState.Open)
                    connection.Close();

                return dtCDCTableColumnss;
            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return null;
            }
        }

        public bool CDCEnabledDB(String sConnectionString,  ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
    
                String sServerName = String.Empty;
                String sDBName = String.Empty;
                sErrorMessage = String.Empty;
                BLL.Utils.ParseConnectionString(sConnectionString, ref sServerName, ref sDBName, ref sErrorMessage);

                command.CommandText = "EXEC sys.sp_cdc_enable_db" ;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                int iResult = command.ExecuteNonQuery();


                if (connection.State == ConnectionState.Open)
                    connection.Close();

                return true;

            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return false;
            }
        }

        public bool CDCDisableDB(String sConnectionString, ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = "EXEC sys.sp_cdc_disable_db";
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                int iResult = command.ExecuteNonQuery();

                if (connection.State == ConnectionState.Open)
                    connection.Close();

                return true;

            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return false;
            }
        }

        public bool CDCDisableCDCTable(String sConnectionString, ref String sDdlDisableTable,ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {

                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sDdlDisableTable;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                int iResult = command.ExecuteNonQuery();

                if (connection.State == ConnectionState.Open)
                    connection.Close();

                return true;

            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();

                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return false;
            }
        }

        public bool CDCEnabledTable(String sConnectionString, String sDDLCDCEnable, ref String sErrorMessage)
        {
            DbConnection connection = null;
            try
            {
                
                DbProviderFactory factory =
                        DbProviderFactories.GetFactory("System.Data.SqlClient");
                connection = factory.CreateConnection();
                connection.ConnectionString = sConnectionString;

                DbCommand command = factory.CreateCommand();
                command.CommandText = sDDLCDCEnable;
                command.Connection = connection;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                int iResult = command.ExecuteNonQuery();

                if (connection.State == ConnectionState.Open)
                    connection.Close();

                return true;

            }
            catch (Exception ex)
            {
                try
                {
                    if (null != connection && connection.State == ConnectionState.Open)
                        connection.Close();
                }
                catch
                {
                }
                sErrorMessage = ex.Message;
                return false;
            }
        }
    }
}

